package com.lhh.company.website.utils;

import com.lhh.company.website.entity.SysException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtils {

    /**
     * 读取Excel
     * @param file
     */
    public static Map<Integer, Map<String,String>> read(File file) throws Exception {

        //            读取xlsx
        Workbook workbook = null;
        Map<Integer, Map<String,String>> map = new HashMap<Integer, Map<String,String>>();
        try {
            //判断excel类型
            String type = FileUtils.getType(file);
            if(type.equals("xlsx")){
                workbook = new XSSFWorkbook(file);
            }else if(type.equals("xls")){
//          读取xls
                workbook = new HSSFWorkbook(new POIFSFileSystem(file));
            }else {
                throw new SysException("不是Excel文件无法解析");
            }

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Map<String,String> sheetMap = new HashMap<>();
                map.put(i,sheetMap);
                for (int j = 0; j < sheet.getLastRowNum()+1; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        for (int k = 0; k < row.getLastCellNum()+1; k++) {
                            Cell cell = row.getCell(k);
                            if (cell != null) {
                                String cellValue = cell.getStringCellValue();
                                sheetMap.put(ExcelUtils.colsNumToChar(k+1)+(j+1),cellValue);
                            }
                        }
                    }
                }
            }
        }catch (Exception e){
            throw e;
        }finally {
            if(workbook != null){
                workbook.close();
            }
        }

        return map;
    }

    public static String colsNumToChar(int i) {
        String aa = "";
        while (i > 0) {
            //通过ascii码将数字转为大写字母
            int base = i % 26;
            boolean flag = false;
            if (base == 0) {
                base = 26;
                flag = true;
            }
            //从64开始往后数
            int ascii = 64+base;

            aa =  (char)ascii + aa;
            i=i/26;
            if(flag){
                i = i-1;
            }
        }
        return aa;
    }
}